package com.bins.cloud.gateway.test;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Slf4j
public class ReadExcel {
    public static void main(String[] args) {
        try {
            // 创建输入流
            String filePath = "D:\\workspace\\pythonworkspace\\1.xlsx";
            FileOutputStream file = new FileOutputStream(new File("D:\\workspace\\pythonworkspace\\2.xlsx"));

            int suffix = filePath.lastIndexOf(".");
            //获取文件后缀名
            String substring = filePath.substring(suffix + 1);
            if (!"xlsx".equals(substring) && !"xls".equals(substring)) {
                System.out.printf("不是excel文件");
            }
            FileInputStream fis = new FileInputStream(filePath);
            // 创建工作簿对象
            Workbook workbook = null;
            if ("xlsx".equals(substring)) {//xlsx
                workbook = WorkbookFactory.create(fis);
            } else {//xls
                workbook = new XSSFWorkbook(fis);
            }

            Sheet sheet = workbook.getSheetAt(0);
            List<QuotationGoods> goodsList1 = Lists.newArrayList();

            for (Row row : sheet) {
                QuotationGoods quotationGoods = new QuotationGoods();
                int i = 0;
                for (Cell cell : row) {
                    switch (i) {
                        case 0:
                            quotationGoods.setGoodsCode(cell.getStringCellValue());
                            break;
                        case 1:
                            quotationGoods.setGoodsName(cell.getStringCellValue());
                            break;
                        case 2:
                            quotationGoods.setQuotationStartDate(cell.getStringCellValue());
                            break;
                        case 3:
                            quotationGoods.setQuotationEndDate(cell.getStringCellValue());
                            break;
                        case 4:
                            quotationGoods.setQuotationAmount(cell.getStringCellValue());
                            break;
                        default:
                            break;
                    }
                    i++;
                }
                goodsList1.add(quotationGoods);
            }
            List<QuotationGoods> goodsList = Lists.newArrayList();
            for (QuotationGoods quotationGoods : goodsList1) {
                if (ObjectUtils.isEmpty(quotationGoods.getQuotationStartDate())) {
                    continue;
                }
                LocalDateTime localDateTime = transferDate(quotationGoods.getQuotationStartDate());
                int month = localDateTime.getMonthValue();
                int day = localDateTime.getDayOfMonth();
                if (month == 1) {
                    if (1 <= day && 15 >= day) {
                        quotationGoods.setQuotationDate("一月上旬");
                    } else {
                        quotationGoods.setQuotationDate("一月下旬");
                    }
                } else if (month == 2) {
                    if (1 <= day && 15 >= day) {
                        quotationGoods.setQuotationDate("二月上旬");
                    } else {
                        quotationGoods.setQuotationDate("二月下旬");
                    }
                } else if (month == 3) {
                    if (1 <= day && 15 >= day) {
                        quotationGoods.setQuotationDate("三月上旬");
                    } else {
                        quotationGoods.setQuotationDate("三月下旬");
                    }
                } else if (month == 4) {
                    if (1 <= day && 15 >= day) {
                        quotationGoods.setQuotationDate("四月上旬");
                    } else {
                        quotationGoods.setQuotationDate("四月下旬");
                    }
                } else if (month == 5) {
                    if (1 <= day && 15 >= day) {
                        quotationGoods.setQuotationDate("五月上旬");
                    } else {
                        quotationGoods.setQuotationDate("五月下旬");
                    }
                }
                goodsList.add(quotationGoods);
            }

            Map<String, List<QuotationGoods>> goodsMap = goodsList.stream().collect(Collectors.groupingBy(QuotationGoods::getGoodsCode));

            Workbook outWorkbook = new XSSFWorkbook();
            Sheet newSheet = outWorkbook.createSheet("Data Sheet");
            newSheet.setColumnWidth(0, 20 * 256);
            newSheet.setColumnWidth(1, 20 * 256);
            Row headerRow = newSheet.createRow(0);
            Cell headerRowCell0 = headerRow.createCell(0);
            headerRowCell0.setCellValue("商品编码");
            Cell headerRowCell1 = headerRow.createCell(1);
            headerRowCell1.setCellValue("商品名称");

            Cell headerRowCell2 = headerRow.createCell(2);
            headerRowCell2.setCellValue("五月下旬");
            Cell headerRowCell3 = headerRow.createCell(3);
            headerRowCell3.setCellValue("五月上旬");
            Cell headerRowCell4 = headerRow.createCell(4);
            headerRowCell4.setCellValue("四月下旬");
            Cell headerRowCell5 = headerRow.createCell(5);
            headerRowCell5.setCellValue("四月上旬");
            Cell headerRowCell6 = headerRow.createCell(6);
            headerRowCell6.setCellValue("三月下旬");
            Cell headerRowCell7 = headerRow.createCell(7);
            headerRowCell7.setCellValue("三月上旬");
            Cell headerRowCell8 = headerRow.createCell(8);
            headerRowCell8.setCellValue("二月下旬");
            Cell headerRowCell9 = headerRow.createCell(9);
            headerRowCell9.setCellValue("二月上旬");
            Cell headerRowCell10 = headerRow.createCell(10);
            headerRowCell10.setCellValue("一月下旬");
            Cell headerRowCell11 = headerRow.createCell(11);
            headerRowCell11.setCellValue("一月上旬");

            int k = 1;
            for (Map.Entry<String, List<QuotationGoods>> entry : goodsMap.entrySet()) {
                List<QuotationGoods> list = entry.getValue();
                if (CollectionUtils.isEmpty(list)) {
                    continue;
                }
                QuotationGoods firstQuotationGoods = list.get(0);
                Row row = newSheet.createRow(k);
                Cell cell = row.createCell(0);
                cell.setCellValue(entry.getKey());
                Cell cell1 = row.createCell(1);
                cell1.setCellValue(firstQuotationGoods.getGoodsName());
                int j = 2;

                List<String> monthList = Arrays.asList("五月下旬", "五月上旬", "四月下旬", "四月上旬", "三月下旬", "三月上旬", "二月下旬", "二月上旬", "一月下旬", "一月上旬");
                for (String month : monthList) {
                    for (QuotationGoods goods : list) {
                        if (month.equals(goods.getQuotationDate())) {
                            Cell cellThree = row.createCell(j);
                            cellThree.setCellValue(fenToYuan(goods.getQuotationAmount()));
                            break;
                        }
                    }
                    j = j + 1;
                }
                k++;
            }
            outWorkbook.write(file);
            // 关闭工作簿和输入流
            workbook.close();
            outWorkbook.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static LocalDateTime transferDate(String dateStr) {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        LocalDateTime localDateTime = LocalDateTime.parse(dateStr, formatter);
        return localDateTime;
    }

    /**
     * 默认四舍五入
     */
    public static final RoundingMode ROUNDING_MODE = RoundingMode.HALF_UP;

    public static String fenToYuan(String bigFen) {
        if (bigFen == null) {
            return getZeroYuan();
        }
        Integer value = Integer.parseInt(bigFen);
        BigDecimal result = new BigDecimal(value).divide(new BigDecimal(100), 2, ROUNDING_MODE);
        // 不足两位小数补0
        DecimalFormat decimalFormat = new DecimalFormat("0.00#");
        return decimalFormat.format(result);
    }

    public static String getZeroYuan() {
        BigDecimal value = new BigDecimal("0");
        // 不足两位小数补0
        DecimalFormat decimalFormat = new DecimalFormat("0.00#");
        return decimalFormat.format(value);
    }
}